CREATE OR REPLACE
procedure        full_backup_time_variant(source_table_name IN varchar2, --源表基表名称
                                             dest_table_name   in varchar2, --目标表基表名称
                                             max_offset        in number, --最大偏移量
                                             min_offset        in number, --最小偏移量
                                             offset_format     in number, --时间格式，1--YYYYMMDD；2--YYYYMM,
                                             ret               out varchar2) AUTHID CURRENT_USER is

  v_first_sysday varchar2(11);
  v_last_sysday  varchar2(11);
  v_source_table varchar2(100);
  v_dest_table   varchar2(100);
  i              number(9);
  v_count        number(12);
  v_tcount       number(2); --表个数，用以判断表是否存在
  v_sql          varchar2(500);
  v_ret          varchar2(1000);
  log_output     varchar2(1000);
  tmp_table      VARCHAR2(100);
begin

  --通过目标表名关联出该表的索引和主外键信息，并通过数组保存，之后删除该表的索引和主外键



  --时间变量
  --max_offset是偏移配置的最大值，min_offset是偏移配置的最小值，对应界面上描述如：1至3，1为最小偏移，3为最大偏移，偏移后，当前时间-最大偏移即为最早的时间点
  --offset_format为时间变量的格式，1--YYYYMMDD；2--YYYYMM；
  v_ret := '"result":{';
  log_output := '"log":{';
  --单表
  if max_offset = -1 and min_offset = -1 then
    v_sql := 'select count(*) as total from ' || source_table_name;
    log_output := log_output || '"执行记录数查询语句":"' || v_sql ;
    EXECUTE IMMEDIATE v_sql
      into v_count;
    log_output := log_output || '","记录数":"' || v_count;

    --判断目标表是否存在，存在则先raname
    v_sql := 'select count(*) from user_tables where table_name=UPPER(''' ||
             dest_table_name || ''')';
    log_output := log_output || '","判断目标表是否存在":"' || v_sql;
    EXECUTE IMMEDIATE v_sql
      into v_tcount;
    if v_tcount = 1 then
        v_sql := 'rename ' || dest_table_name || ' to ' || dest_table_name || '_bak' || to_char(sysdate,'ddhh24miss');
        log_output := log_output || '","已存在目标表改名":"' || v_sql;
      EXECUTE IMMEDIATE v_sql;
    end if;

    --将源表重命名为目标表,并重构源表
    v_sql := 'rename ' || source_table_name || ' to ' || dest_table_name;
    log_output := log_output || '","执行rename语句":"' || v_sql;
    EXECUTE IMMEDIATE v_sql;
      v_sql := 'create table ' || source_table_name || ' as select * from ' || dest_table_name || ' where 1=0';
      log_output := log_output || '","重建表结构":"' || v_sql;
      EXECUTE IMMEDIATE v_sql;
    v_ret := v_ret || '"' || source_table_name || '":"' || v_count || '"';



   DECLARE
   constraintsname varchar(100);--约束名
   columnname varchar(100);--字段名
   columnnameR varchar(100);--外键关联的字段
   tablenameR varchar(100);--外键关联的表名
   constraintstype varchar(100);--
   ctype varchar(100);   --约束类型 key：主外键  index：索引
   pkey char(30);
   TYPE c1 IS REF CURSOR;
   temp_cursor c1;




begin

 --主键
	ctype := 'key';
	OPEN temp_cursor
		FOR
			SELECT user_cons_columns.constraint_name,column_name,constraint_type
			FROM USER_CONSTRAINTS LEFT JOIN  user_cons_columns ON user_cons_columns.constraint_name = USER_CONSTRAINTS.constraint_name
			WHERE USER_CONSTRAINTS.TABLE_name = UPPER(dest_table_name) AND constraint_type='P';
	LOOP
         FETCH temp_cursor INTO constraintsname, columnname,constraintstype;--获得当前记录的数据
    EXIT WHEN temp_cursor%NOTFOUND;
    	v_sql := 'ALTER TABLE '|| dest_table_name || ' DROP CONSTRAINT ' || constraintsname;
        log_output := log_output || '","遍历主键，并删除目标表主键":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;
    	v_sql := 'alter table '|| source_table_name ||' add constraint '||constraintsname||' primary key('||columnname||')';
        log_output := log_output || '","为源表加上主键":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;
	pkey :=  columnname;
    END loop;



	--外键
	OPEN temp_cursor
		FOR
			select
			       t2.table_name,
			       t1.constraint_name,
			       a1.column_name,
			       a2.column_name as "COLUMN_NAME(R)",
			       t1.constraint_type
			from user_constraints t1, user_constraints t2, user_cons_columns a1, user_cons_columns a2
			where
				t1.r_constraint_name = t2.constraint_name and
				t1.constraint_name = a1.constraint_name and
				t1.r_constraint_name = a2.constraint_name  AND
				t1.TABLE_NAME = UPPER(dest_table_name);
	LOOP
         FETCH temp_cursor INTO tablenameR,constraintsname, columnname,columnnameR,constraintstype;--获得当前记录的数据
    EXIT WHEN temp_cursor%NOTFOUND;
    	v_sql := 'ALTER TABLE '|| dest_table_name || ' DROP CONSTRAINT ' || constraintsname;
	log_output := log_output || '","遍历外键，并删除目标表外键":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;
    	v_sql := 'alter table '|| source_table_name ||' add constraint '||constraintsname||' foreign key('||columnname||') references '||tablenameR||'('||columnnameR||')';
        log_output := log_output || '","为源表加上外键":"' || v_sql;
        EXECUTE IMMEDIATE v_sql;
        END loop;
	COMMIT;



	--索引
	ctype := 'index';
	OPEN temp_cursor
		FOR
			SELECT user_indexes.index_name,column_name,user_indexes.uniqueness
			FROM user_indexes LEFT JOIN  user_ind_columns ON user_indexes.index_name = user_ind_columns.index_name
			WHERE user_indexes.TABLE_name = UPPER(dest_table_name);
	LOOP
         FETCH temp_cursor INTO constraintsname, columnname,constraintstype;--获得当前记录的数据
    EXIT WHEN temp_cursor%NOTFOUND;
    	v_sql := 'drop INDEX ' || constraintsname;
	log_output := log_output || '","遍历索引，并删除目标表索引":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;



	IF constraintstype = 'UNIQUE' then --判断是否唯一索引
    	    IF columnname <> pkey THEN  --主键会自动创建唯一索引，所以不需要重新创建
    	  		 v_sql := 'create index UNIQUE '||constraintsname||' on '||source_table_name||'('||columnname||')';
    		END IF;
	else
             	         v_sql := 'create index '||constraintsname||' on '||source_table_name||'('||columnname||')';
        end if;
        log_output := log_output || '","为源表加上索引":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;

        END loop;
	COMMIT;
CLOSE temp_cursor;

end;
  else
    --分表
    --循环每个分表
    for i in -max_offset .. -min_offset loop
      --YYYYMMDD格式的偏移
      if offset_format = 1 then
        select to_char(sysdate + i, 'yyyymmdd')
          into v_first_sysday
          from dual;
      else
        --YYYYMM格式的偏移
        select to_char(add_months(sysdate, i), 'yyyymm')
          into v_first_sysday
          from dual;
      end if;
      --偏移到最早的时间(最大偏移量)
      if i = max_offset then
        select source_table_name || '_' || v_first_sysday,
               dest_table_name || '_' || v_first_sysday
          into v_source_table, v_dest_table
          from dual;
      else
        --中间偏移量
        v_last_sysday := v_first_sysday;
        select source_table_name || '_' || v_last_sysday,
               dest_table_name || '_' || v_last_sysday
          into v_source_table, v_dest_table
          from dual;
      end if;
      dbms_output.put_line(v_source_table);
      dbms_output.put_line(v_dest_table);

      v_sql := 'select count(*) as total from ' || v_source_table;
      log_output := log_output || '"执行记录数查询语句":"' || v_sql;
      EXECUTE IMMEDIATE v_sql
        into v_count;
      log_output := log_output || '","记录数":"' || v_sql;

      --判断目标表是否存在，存在则先drop
      v_sql := 'select count(*) from user_tables where table_name=UPPER(''' ||
               v_dest_table || ''')';
      log_output := log_output || '","判断目标表是否存在":"' || v_sql;
      EXECUTE IMMEDIATE v_sql
        into v_tcount;
      if v_tcount = 1 then
        v_sql := 'rename ' || v_dest_table || ' to ' || v_dest_table || '_bak' || to_char(sysdate,'ddhh24miss');
        log_output := log_output || '","已存在目标表改名":"' || v_sql;
        EXECUTE IMMEDIATE v_sql;
      end if;

      v_sql := 'rename ' || v_source_table || ' to ' || v_dest_table;
      log_output := log_output || '","执行rename语句":"' || v_sql;
      EXECUTE IMMEDIATE v_sql;
      v_sql := 'create table ' || v_source_table || ' as select * from ' || v_dest_table || ' where 1=0';
      log_output := log_output || '","重建表结构":"' || v_sql;
      EXECUTE IMMEDIATE v_sql;
      if i = -max_offset then
        v_ret := v_ret || '"' || v_source_table || '":"' || v_count || '"';
      else
        v_ret := v_ret || ',"' || v_source_table || '":"' || v_count || '"';
      end if;

DECLARE
   constraintsname varchar(100);--约束名
   columnname varchar(100);--字段名
   columnnameR varchar(100);--外键关联的字段
   tablenameR varchar(100);--外键关联的表名
   constraintstype varchar(100);--
   ctype varchar(100);   --约束类型 key：主外键  index：索引
   pkey char(30);
   TYPE c1 IS REF CURSOR;
   temp_cursor c1;




begin

 --主键
	ctype := 'key';
	OPEN temp_cursor
		FOR
			SELECT user_cons_columns.constraint_name,column_name,constraint_type
			FROM USER_CONSTRAINTS LEFT JOIN  user_cons_columns ON user_cons_columns.constraint_name = USER_CONSTRAINTS.constraint_name
			WHERE USER_CONSTRAINTS.TABLE_name = UPPER(dest_table_name) AND constraint_type='P';
	LOOP
         FETCH temp_cursor INTO constraintsname, columnname,constraintstype;--获得当前记录的数据
    EXIT WHEN temp_cursor%NOTFOUND;
    	v_sql := 'ALTER TABLE '|| dest_table_name || ' DROP CONSTRAINT ' || constraintsname;
        log_output := log_output || '","遍历主键，并删除目标表主键":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;
    	v_sql := 'alter table '|| source_table_name ||' add constraint '||constraintsname||' primary key('||columnname||')';
        log_output := log_output || '","为源表加上主键":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;
	pkey :=  columnname;
    END loop;



	--外键
	OPEN temp_cursor
		FOR
			select
			       t2.table_name,
			       t1.constraint_name,
			       a1.column_name,
			       a2.column_name as "COLUMN_NAME(R)",
			       t1.constraint_type
			from user_constraints t1, user_constraints t2, user_cons_columns a1, user_cons_columns a2
			where
				t1.r_constraint_name = t2.constraint_name and
				t1.constraint_name = a1.constraint_name and
				t1.r_constraint_name = a2.constraint_name  AND
				t1.TABLE_NAME = UPPER(dest_table_name);
	LOOP
         FETCH temp_cursor INTO tablenameR,constraintsname, columnname,columnnameR,constraintstype;--获得当前记录的数据
    EXIT WHEN temp_cursor%NOTFOUND;
    	v_sql := 'ALTER TABLE '|| dest_table_name || ' DROP CONSTRAINT ' || constraintsname;
	log_output := log_output || '","遍历外键，并删除目标表外键":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;
    	v_sql := 'alter table '|| source_table_name ||' add constraint '||constraintsname||' foreign key('||columnname||') references '||tablenameR||'('||columnnameR||')';
        log_output := log_output || '","为源表加上外键":"' || v_sql;
        EXECUTE IMMEDIATE v_sql;
        END loop;
	COMMIT;



	--索引
	ctype := 'index';
	OPEN temp_cursor
		FOR
			SELECT user_indexes.index_name,column_name,user_indexes.uniqueness
			FROM user_indexes LEFT JOIN  user_ind_columns ON user_indexes.index_name = user_ind_columns.index_name
			WHERE user_indexes.TABLE_name = UPPER(dest_table_name);
	LOOP
         FETCH temp_cursor INTO constraintsname, columnname,constraintstype;--获得当前记录的数据
    EXIT WHEN temp_cursor%NOTFOUND;
    	v_sql := 'drop INDEX ' || constraintsname;
	log_output := log_output || '","遍历索引，并删除目标表索引":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;



	IF constraintstype = 'UNIQUE' then --判断是否唯一索引
    	    IF columnname <> pkey THEN  --主键会自动创建唯一索引，所以不需要重新创建
    	  		 v_sql := 'create index UNIQUE '||constraintsname||' on '||source_table_name||'('||columnname||')';
    		END IF;
	else
             	         v_sql := 'create index '||constraintsname||' on '||source_table_name||'('||columnname||')';
        end if;
        log_output := log_output || '","为源表加上索引":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;

        END loop;
	COMMIT;
CLOSE temp_cursor;

end;


    end loop;
  end if;





  v_ret := '{' || v_ret || '},' || log_output || '"}}';
  ret := v_ret;



EXCEPTION
  WHEN OTHERS THEN
    ret := '{' || log_output || '"},"error":"' || sqlcode || sqlerrm || '"}';
end full_backup_time_variant;